from flask import Flask
from flask_sqlalchemy import SQLAlchemy

"""
    多对多，学生-课程
"""


app =Flask(__name__)

app.config["SQLALCHEMY_DATABASE_URI"] = "mysql+pymysql://root:000000@hadoop102:3306/data"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
db = SQLAlchemy(app)

# 中间表
tb_student_course = db.Table(
    "tb_student_course",
    db.Column("student_id",db.Integer,db.ForeignKey("students.id")),
    db.Column("course_id",db.Integer,db.ForeignKey("courses.id"))
)


# 学生
class Student(db.Model):
    __tablename__ = "students"
    id = db.Column(db.Integer,primary_key=True)
    name = db.Column(db.String(32))
    # 关系属性 secondary使用在多对多，使用在二次查询
    courses = db.relationship("Course",backref="students",secondary=tb_student_course)
    def __repr__(self):
        return "<Student:%s>"%self.name

# 课程
class Course(db.Model):
    __tablename__ = "courses"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(32))
    def __repr__(self):
        return "<Course:%s>"%self.name



@app.route("/")
def hello():
    return "hello"

if __name__ == '__main__':
    db.drop_all()
    db.create_all()
    # 添加测试数据

    stu1 = Student(name='张三')
    stu2 = Student(name='李四')
    stu3 = Student(name='王五')

    cou1 = Course(name='物理')
    cou2 = Course(name='化学')
    cou3 = Course(name='生物')

    stu1.courses = [cou2, cou3]
    stu2.courses = [cou2]
    stu3.courses = [cou1, cou2, cou3]

    db.session.add_all([stu1, stu2, stu2])
    db.session.add_all([cou1, cou2, cou3])

    db.session.commit()
    app.run(debug=True)